import pandas as pd
import numpy as np
import os

# 读取数据（请根据实际路径调整）
data_path = 'F:/安装包/PPD-First-Round-Data-Updated/PPD-First-Round-Data-Update/data_input1.csv'
df = pd.read_csv(data_path, encoding='gb18030')

# 初始化变量明细表
variable_info = pd.DataFrame(columns=[
    '变量名称', '变量类型', '总样本数', '非空数', '缺失数', '缺失率',
    '唯一值数量', '典型值（前5个）', '与目标相关性（Spearman）'
])

# 遍历每一列获取信息
for col in df.columns:
    series = df[col]
    total = len(series)
    missing = series.isnull().sum()
    missing_rate = missing / total if total != 0 else 0
    unique_vals = series.nunique()

    dtype = series.dtype.name
    sample_values = series.dropna().unique()[:5]
    sample_display = ', '.join(map(str, sample_values)) if len(sample_values) > 0 else '无有效值'

    # 检查是否为数值列且非常量后再计算相关性
    corr = np.nan
    if dtype in ['int64', 'float64'] and 'target' in df.columns:
        if unique_vals > 1:  # 非常量列
            try:
                corr = series.corr(df['target'], method='spearman')
            except Exception as e:
                print(f"计算列 '{col}' 的相关性时出错: {e}")

    variable_info = variable_info._append({
        '变量名称': col,
        '变量类型': dtype,
        '总样本数': total,
        '非空数': total - missing,
        '缺失数': missing,
        '缺失率': f"{missing_rate:.2%}",
        '唯一值数量': unique_vals,
        '典型值（前5个）': sample_display,
        '与目标相关性（Spearman）': f"{corr:.4f}" if not np.isnan(corr) else 'N/A'
    }, ignore_index=True)

# 按缺失率降序排序
variable_info = variable_info.sort_values('缺失率', ascending=False).reset_index(drop=True)

# 保存结果到与输入文件相同的目录
output_dir = os.path.dirname(data_path)
output_path = os.path.join(output_dir, 'var_info.csv')
variable_info.to_csv(output_path, encoding='utf-8', index=False)

print(f"变量明细表已成功保存至: {output_path}")